The invention relates to the physical design of a database.
Database systems are typically not self-optimizing in terms of their physical design. That is, they do not automatically alter the particular layout and storage characteristics of their data and operations. Most systems do include features that allow a user to specify certain settings, such as a choice of indexed or sequential keys for data records, the expected size of data files, or the storage locations of the data and program files. However, the appropriate settings of these and other features usually requires unique expertise that a general user does not possess.
A human database expert, therefore, is usually required to fine tune the physical design of a database in order to provide better runtime performance. For example, the expert can make some improvements that yield a higher application throughput (i.e., a greater number of transactions processed) by reducing the number of necessary input/output operations (I/Os) and conducting more efficient buffering of data in memory. Other improvements include providing more efficient utilization of the central processing unit (CPU) and disk storage units by partitioning data across multiple disks, calculating optimal data density (i.e., providing large enough storage areas to contain each section of data), and calculating optimal placement of data within the data files.
As noted, the physical structure of a database can be defined as the particular storage characteristics of a collection of interrelated data stored as one or more stored records. A stored record is the basic unit of storage which corresponds to one logical record and contains all pointers, record lengths, and other identifiers necessary to represent pieces of data. Aspects of the physical structure of a database include, for example, the location and size of files, the access methods and keys to database records, and the placement of records within the database. Not surprisingly, the design of the physical structure has a significant impact on performance of the database as a whole. This impact, however, can differ from database to database (even among databases having the same physical structure). For example, the physical structure's impact on performance can vary depending on the amount of data in the database (i.e., the "data volume"). The impact can also vary depending on the "physical constraints" of the system, e.g., the number of storage devices available, the amount of space available on the storage devices, the amount of I/O taking place, and the amount of random access memory (RAM) available. And finally, the impact can vary depending on the "workload" of the database, e.g., the type, frequency, priority, and sequence of database operations.